#region imported Namespaces
using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using BO = PHSRAG.Insight21.BusinessObjects;
using DataAccess = PHSRAG.Utility.DataAccess;
using ICachePolicy = PHSRAG.Insight21.Policy.ICachePolicy;
using SearchCriteria = PHSRAG.Insight21.SearchContext.SearchCriteria;
using PersonnelConstants = PHSRAG.Insight21.Personnel.Constants;
#endregion

namespace PHSRAG.Insight21.Personnel
{
	#region Class Personnel Salaries
	/// <summary>
	/// The PersonnelSalaries object supports loading of salary information
	/// for all personnel to which the user has access. It also exposes methods to 
	/// support sorting and searching on the retrieved data.
	/// </summary>
	public class PersonnelSalaries :BO.BusinessObject
	{
		#region Constants
		private const string	SalaryGridsKey				= "SalaryGrids_DF0F5149-F492-4941-A596-E65AEF5D4550";
		private const string	DefaultSortColumn			= "EmployeeName";
		private const int		DomainUserIdArgumentIndex	= 0;
		private const int		FiscalYearIdArgumentIndex	= 1;
		private const int		EmployeeJobMapIdIndex		= 2;
		private const int		SalaryDataTableIndex		= 0;
		private const string	SalaryTotalsTableName		= "SalaryTotals";
		private const string	CurrentSalaryColumnName		= "CurrentSalary";
		private const string	SalaryColumnName			= "TotalSalary";
		private const string	ResearchGrantColumnName		= "ResearchGrant";
		private const string	ResearchSundryColumnName	= "ResearchSundry";
		private const string	DiscretionaryColumnName		= "Discretionary";
		private const string	OperatingFundsColumnName	= "OperatingFunds";
		private const string	POFundsColumnName			= "POFunds";
		private const string	HMSFundsColumnName			= "HMSFunds";
		private const string	VAColumnName				= "VA";
		private const string	OtherColumnName				= "Other";
		private const string	JobMapIdColumnName			= "EmployeeJobMapId";
		#endregion

		#region Instance Variables
		private Hashtable		salaryGridMap			= new Hashtable();
		private string			sortColumn				= DefaultSortColumn;
		private string			currentDataTableName	= Constants.OriginalDataTableName;
		private bool			sortAscending			= true;
		#endregion

		#region Constructor
		/// <summary>
		/// Intialize the base class with the prevalent cache policy
		/// </summary>
		/// <param name="cachePolicy">The prevalent cache policy</param>
		public PersonnelSalaries() : base(null)
		{
		}
		#endregion

		#region Public Properties
		/// <summary>
		/// Get the key to be used in all caching operations for this instance of the object.
		/// </summary>
		public static string CacheKey	{ get { return SalaryGridsKey; } }
		/// <summary>
		/// Get the total number of salary grid rows in the data container.
		/// Returns 0 if there is no data in the data container
		/// </summary>
		public int Count
		{
			get { return (DataContainer.Tables.Count == 0) ? 0 : DataContainer.Tables[currentDataTableName].Rows.Count; }
		}
		/// <summary>
		/// Get the sort sequence.
		/// </summary>
		public bool SortAscending
		{
			get { return sortAscending; }
		}

		/// <summary>
		/// Get the name of the sort column.
		/// </summary>
		public string SortColumn
		{
			get { return sortColumn; }
		}
		/// <summary>
		/// Get the table name of the active table in the data container
		/// This will be used for selective downloading of data into reports
		/// </summary>
		public string CurrentDataTableName
		{
			get { return currentDataTableName;}
		}
		#endregion

		#region BusinessObject Methods
		/// <summary>
		///	Load the salary grid data for the current user and set the data table names
		/// Sort the laoded data on the default sort column
		///	For the load the domain user id is mandatory
		///	Compute the totals of various columns in the datagrid and
		///	add a new datatable with the totals to the datacontainer.
		/// </summary>
		/// <param name="connectionString">Database connection string</param>
		/// <param name="args">Is expected to contain the domain user ID,FiscalYearId and EmployeeJobMapId</param>
		public override void Load(string connectionString, params object[] args)
		{
			if(args.Length != 3)
				throw new Exception(@"Salary Grid load requires domain user id,FiscalYearId and EmployeeJobMapId.");

			DataContainer = DataAccess.GetDataSet(
				connectionString
				,Constants.GetSalaryGridSPROCName
				,DataAccess.BuildSqlParameterArray
				("@domainUserID",SqlDbType.Int,(int)args[DomainUserIdArgumentIndex]
				,"@fiscalYearID",SqlDbType.Int,(int)args[FiscalYearIdArgumentIndex]));

			if(DataContainer.Tables.Count == 1)
			{
				DataContainer.Tables[SalaryDataTableIndex].TableName = Constants.OriginalDataTableName;
				AddTableWithDistinctRows(DataContainer.Tables[Constants.OriginalDataTableName]);
				ComputeSalaryTotals();
			}
			else
				throw new Exception(@"No Salary Details found for the user");
		}
		#endregion

		#region Public Methods
		/// <summary>
		/// Get the list of SalaryGrid objects bound by a start and end index. 
		/// These indeces are into the list of salary grids that was loaded during the 
		/// Load() method execution into the data container. Typically, the start index is on 
		/// a "page" boundary, where the "page" referes to a datagrid page.
		/// A Hashtable of ArrayList objects is maintained using the start index as the key. 
		/// This allows retrieval of the set of SalaryGrid objects associated with that 
		/// index, thereby directly supporting random access based the page index.
		/// </summary>
		/// <param name="startIndex">Index within the salary grid list to begin at</param>
		/// <param name="count">Total number of SalaryGrid objects to load</param>
		/// <returns>The list of SalaryGrid objects</returns>
		public ArrayList GetSalaryGridList(int startIndex, int count)
		{
			ArrayList salaryGridList = salaryGridMap[startIndex] as ArrayList;
			if (salaryGridList == null)
			{
				salaryGridList = new ArrayList();
				DataTable salaryGridTable = DataContainer.Tables[currentDataTableName];
				for (int indexCount = startIndex, end = Math.Min(indexCount + count, salaryGridTable.Rows.Count); indexCount < end; ++indexCount)
					salaryGridList.Add(new PersonnelSalary(CachePolicy, salaryGridTable.Rows[indexCount]));
				salaryGridMap[startIndex] = salaryGridList;
			}
			return salaryGridList;
		}

		/// <summary>
		/// Search the DataContainer for rows which match the specified search criteria.
		/// Two DataTables are maintained within the DataContainer; a full-complement DataTable and a filtered one.
		/// This allows filtering without having to hit the database. If a filter is in
		/// effect, we simply set the value of the currentTableName to Constants.FilteredDataTableName so that the
		/// GetSalaryGridList() method may retreive from the appropriate DataTable.
		///	ReCompute the totals of various columns in the datagrid and
		///	add a new datatable with the totals to the datacontainer.
		/// </summary>
		/// <param name="searchCriteria">The search criteria instance picked from the search context</param>
		/// <remarks>True if the search criteria did any filtering; false otherwise</remarks>
		public bool Search(SearchCriteria searchCriteria, int JobMapId)
		{
			salaryGridMap.Clear();

			StringBuilder filter = new StringBuilder();

			foreach (string key in searchCriteria.Keys)
			{
				switch (key.ToLower())
				{
					case "employeefirstname":
						filter.AppendFormat("{0} FirstName LIKE '{1}%'",(filter.Length>0)?"AND":"",searchCriteria[key]);
						break;
					case "employeelastname":
						filter.AppendFormat("{0} LastName LIKE '{1}%'",(filter.Length>0)?"AND":"",searchCriteria[key]);
						break;
					case "employeenumber":
						filter.AppendFormat("{0} EmployeeNumber LIKE '{1}%'",(filter.Length>0)?"AND":"",searchCriteria[key]);
						break;
					case "jobcode":
						filter.AppendFormat("{0} JobCode='{1}'",(filter.Length>0)?"AND":"",searchCriteria[key]);
						break;
					case "status":
						filter.AppendFormat("{0} StatusID='{1}'",(filter.Length>0)?"AND":"",searchCriteria[key]);
						break;
					case "hrdepartment":
						filter.AppendFormat("{0} HRDepartmentID='{1}'",(filter.Length>0)?"AND":"",searchCriteria[key]);
						break;
					case "payfrequencytype":
						filter.AppendFormat("{0} PayFrequencyTypeID='{1}'",(filter.Length>0)?"AND":"",searchCriteria[key]);
						break;
					case "effectivesalarydatefrom":
						filter.AppendFormat("{0} EffectiveDate>='{1}'",(filter.Length>0)?"AND":"",searchCriteria[key]);
						break;
					case "effectivesalarydateto":
						filter.AppendFormat("{0} EffectiveDate<='{1}'",(filter.Length>0)?"AND":"",searchCriteria[key]);
						break;
					case "pi":
						filter.AppendFormat("{0} PIID ='{1}'",(filter.Length>0)?"AND":"",searchCriteria[key]);
						break;
					case "lab":
						filter.AppendFormat("{0} LabID ='{1}'",(filter.Length>0)?"AND":"",searchCriteria[key]);
						break;
				}
			}

			if (JobMapId != 0)
			{
				filter.AppendFormat("{0} EmployeeJobMapID ='{1}'",(filter.Length>0)?"AND":"",JobMapId);
			}

			bool applySearch = (filter.Length > 0);
			DataTable salaryGridTable = DataContainer.Tables[Constants.OriginalDataTableName];
			if(applySearch)
			{
				DataTable filteredDataTable = salaryGridTable.Clone();
				filteredDataTable.TableName = currentDataTableName = Constants.FilteredDataTableName;

				DataRow[] filteredRows = salaryGridTable.Select(filter.ToString());
				foreach (DataRow salaryGridRow in filteredRows)
					filteredDataTable.ImportRow(salaryGridRow);

				AddTableWithDistinctRows(filteredDataTable);
			}
			else
				AddTableWithDistinctRows(salaryGridTable);
			
			ComputeSalaryTotals();
			return applySearch;
		}
		/// <summary>
		/// Sort the list of salary grids by the speicified column.
		/// If the column is to be sorted is the same as what was last sorted on, the sort sequence is toggled. Else, the
		/// sort sequence is set to ascending.
		/// </summary>
		/// <param name="columnName">Column to sort by</param>
		public void Sort(string columnName)
		{
			salaryGridMap.Clear();

			sortAscending = (sortColumn == columnName) ? !sortAscending : true;
			sortColumn = columnName;

			ProcessSort();
		}
		#endregion

		# region Private Methods
		/// <summary>
		/// ComputeSalaryTotals method is used to compute the totals of following columns in the datagrid
		/// CurrentSalary; TotalSalary;ResearchGrant;ResearchSundry;Discretionary;OperatingFunds;
		/// POFunds;HMSFunds;VA and Other.
		/// Check if the DataContainer contains the totalsSalaryTable, if exists clear the existing values 
		/// otherwise create the totals by creating an in memory table with a column for each total.
		/// Add the datatable to the datacontainer.
		/// </summary>
		private void ComputeSalaryTotals()
		{
			DataTable totalSalaryTable;
			if(! DataContainer.Tables.Contains(SalaryTotalsTableName))
			{
				totalSalaryTable = new DataTable(SalaryTotalsTableName);
				
				totalSalaryTable.Columns.Add(new DataColumn(CurrentSalaryColumnName,
					System.Type.GetType("System.Decimal")));
				totalSalaryTable.Columns.Add(new DataColumn(SalaryColumnName,
					System.Type.GetType("System.Decimal")));
				totalSalaryTable.Columns.Add(new DataColumn(ResearchGrantColumnName,
					System.Type.GetType("System.Decimal")));
				totalSalaryTable.Columns.Add(new DataColumn(ResearchSundryColumnName,
					System.Type.GetType("System.Decimal")));
				totalSalaryTable.Columns.Add(new DataColumn(DiscretionaryColumnName,
					System.Type.GetType("System.Decimal")));
				totalSalaryTable.Columns.Add(new DataColumn(OperatingFundsColumnName,
					System.Type.GetType("System.Decimal")));
				totalSalaryTable.Columns.Add(new DataColumn(POFundsColumnName,
					System.Type.GetType("System.Decimal")));
				totalSalaryTable.Columns.Add(new DataColumn(HMSFundsColumnName,
					System.Type.GetType("System.Decimal")));
				totalSalaryTable.Columns.Add(new DataColumn(VAColumnName ,
					System.Type.GetType("System.Decimal")));
				totalSalaryTable.Columns.Add(new DataColumn(OtherColumnName,
					System.Type.GetType("System.Decimal")));

				DataContainer.Tables.Add(totalSalaryTable);
			}
			else
			{
				totalSalaryTable = DataContainer.Tables[SalaryTotalsTableName];
				totalSalaryTable.Clear();
			}
			
			DataView salaryTable = DataContainer.Tables[currentDataTableName].DefaultView;
		
			if (salaryTable.Count > 0)
			{
				DataRow row = totalSalaryTable.NewRow();
				row[CurrentSalaryColumnName] = 
					(decimal)salaryTable.Table.Compute(string.Format("Sum([{0}])",CurrentSalaryColumnName),string.Empty);
				row[SalaryColumnName] = 
					(decimal)salaryTable.Table.Compute(string.Format("Sum([{0}])",SalaryColumnName),string.Empty);
				row[ResearchGrantColumnName] = 
					(decimal)salaryTable.Table.Compute(string.Format("Sum([{0}])",ResearchGrantColumnName),string.Empty);
				row[ResearchSundryColumnName] = 
					(decimal)salaryTable.Table.Compute(string.Format("Sum([{0}])",ResearchSundryColumnName),string.Empty);
				row[DiscretionaryColumnName] = 
					(decimal)salaryTable.Table.Compute(string.Format("Sum([{0}])",DiscretionaryColumnName),string.Empty);
				row[OperatingFundsColumnName] = 
					(decimal)salaryTable.Table.Compute(string.Format("Sum([{0}])",OperatingFundsColumnName),string.Empty);
				row[POFundsColumnName] = 
					(decimal)salaryTable.Table.Compute(string.Format("Sum([{0}])",POFundsColumnName),string.Empty);
				row[HMSFundsColumnName] = 
					(decimal)salaryTable.Table.Compute(string.Format("Sum([{0}])",HMSFundsColumnName),string.Empty);
				row[VAColumnName] = 
					(decimal)salaryTable.Table.Compute(string.Format("Sum([{0}])",VAColumnName),string.Empty);
				row[OtherColumnName] = 
					(decimal)salaryTable.Table.Compute(string.Format("Sum([{0}])",OtherColumnName),string.Empty);

				totalSalaryTable.Rows.Add(row);
			}
		}
		/// <summary>
		/// Sorts the current data table on the sortColumn instance variable
		/// in the order specified by the sortOrder instance variable
		/// </summary>
		private void ProcessSort()
		{
			DataTable salaryGridTable = DataContainer.Tables[currentDataTableName];
			DataView salaryGridView = salaryGridTable.DefaultView;
			salaryGridView.Sort = string.Format("{0} {1}", 
				sortColumn, sortAscending ? Constants.SortOrderAscending : Constants.SortOrderDescending);

			DataTable sortedDataTable = salaryGridTable.Clone();
			foreach (DataRowView salaryGridRowView in salaryGridView)
				sortedDataTable.ImportRow(salaryGridRowView.Row);

			if(DataContainer.Tables.Contains(currentDataTableName))
				DataContainer.Tables.Remove(currentDataTableName);
			
			DataContainer.Tables.Add(sortedDataTable);
		}
		/// <summary>
		/// Add a table with distinct rows to the data container
		/// </summary>
		/// <param name="summaryTable">datatable with personnel summary data,
		/// from which distinct rows will be picked</param>
		private void AddTableWithDistinctRows(DataTable salaryGridTable)
		{
			DataTable distinctTable		= salaryGridTable.Clone();
			distinctTable.TableName		= currentDataTableName = Constants.FilteredDataTableName;

			if(salaryGridTable.Rows.Count >= 1)
			{
				//Get the employeeJobMapID from the first row
				int employeeJobMapID = (int)salaryGridTable.Rows[0][JobMapIdColumnName];
			
				//Add the first row to the table
				distinctTable.ImportRow(salaryGridTable.Rows[0]);

				foreach (DataRow salaryRow in salaryGridTable.Rows)
				{
					//If the employeeJobMapID changes reset the value in the local variable
					//and import the row to the distinct data table
					if(employeeJobMapID != (int)salaryRow[JobMapIdColumnName])
					{
						employeeJobMapID = (int)salaryRow[JobMapIdColumnName];
						distinctTable.ImportRow(salaryRow);
					}
				}
			}						
			if(DataContainer.Tables.Contains(currentDataTableName))
				DataContainer.Tables.Remove(currentDataTableName);
			DataContainer.Tables.Add(distinctTable);

			ProcessSort();
		}
		#endregion
	}
	#endregion
}
